package orxatas.travelme.databases;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Base de datos para gestionar localmente la información de Travelme.
 * Convenio: sólo la deben utilizar las clases de sincronización.
 * */
public class TravelmeDatabase extends SQLiteOpenHelper {
	/**
	 * Nombre de la base de datos.
	 * */
	static final public String DATABASE_NAME = "travelmedb";
	
	/**
	 * Versión de la base de datos.
	 * */
	static final public int DATABASE_VERSION = 1;
	
	static final public String GROUP_TNAME = "`group`";
	static final public String GROUP_CID = "idgroup";
	static final public String GROUP_CIDL = "idgrouplocal";
	static final public String GROUP_CGROUPNAME = "name";
	static final public String GROUP_COWNER = "owner";
	static final public String GROUP_CLASTUPDATE = "lastupdate";
	static final public String GROUP_TOREMOVE = "toremove";
	//static final private String TABLE_GROUP_CREATION = "CREATE TABLE "+GROUP_TNAME+" ("+GROUP_CID+" INTEGER PRIMARY KEY, "+GROUP_CIDL+" INTEGER , "+GROUP_CGROUPNAME+" VARCHAR(50), "+GROUP_COWNER+" INTEGER , "+GROUP_CLASTUPDATE+" INTEGER)";
	static final private String TABLE_GROUP_DELETE = "DROP TABLE IF EXISTS "+GROUP_TNAME;
	
	static final private String TABLE_GROUP_CREATION = "CREATE TABLE "+GROUP_TNAME+"("+
			"  "+GROUP_CIDL+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
			"  "+GROUP_CID+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+GROUP_CGROUPNAME+" VARCHAR(50) NOT NULL,"+
			"  "+GROUP_COWNER+" INTEGER NOT NULL,"+
			"  "+GROUP_CLASTUPDATE+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+GROUP_TOREMOVE+" BOOL NOT NULL DEFAULT 0,"+
			"  CONSTRAINT 'GROUP_CIDL_UNIQUE'"+
			"    UNIQUE("+GROUP_CIDL+"),"+
			"  CONSTRAINT 'GROUP_CID_UNIQUE'"+
			"    UNIQUE("+GROUP_CID+")"+
			");";
	
	static final public String COMMENT_TNAME = "comment";
	static final public String COMMENT_CID = "idcomment";
	static final public String COMMENT_CIDL = "idcommentlocal";
	static final public String COMMENT_CIDGROUP = "idgroup";
	static final public String COMMENT_CAUTOR = "autor";
	static final public String COMMENT_CC = "comment";
	static final public String COMMENT_CIDPLACE = "idplace";
	static final public String COMMENT_CLASTUPDATE = "lastupdate";
	static final public String COMMENT_CSYNC = "sync";
	static final private String TABLE_COMMENT_CREATION = "CREATE TABLE "+COMMENT_TNAME+"("+
			"  "+COMMENT_CIDL+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
			"  "+COMMENT_CID+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+COMMENT_CAUTOR+" INTEGER NOT NULL,"+
			"  "+COMMENT_CC+" TEXT NOT NULL,"+
			"  "+COMMENT_CIDPLACE+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+COMMENT_CLASTUPDATE+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+COMMENT_CSYNC+" BOOL NOT NULL DEFAULT 0,"+
			"  "+COMMENT_CIDGROUP+" INTEGER NOT NULL,"+
			"  CONSTRAINT 'COMMENT_CIDL_UNIQUE'"+
			"    UNIQUE("+COMMENT_CIDL+")"+
			");";
	static final private String TABLE_COMMENT_DELETE = "DROP_TABLE IF EXISTS "+COMMENT_TNAME;
	
	static final public String FRIEND_TNAME = "friend";
	static final public String FRIEND_CID = "idfriend"; //Reference to `user` table.
	static final private String TABLE_FRIEND_CREATION = "CREATE TABLE "+FRIEND_TNAME+" ("+FRIEND_CID+" INT PRIMARY KEY)";
	static final private String TABLE_FRIEND_DELETE = "DROP TABLE IF EXISTS "+FRIEND_TNAME;
	
	static final public String USERINGROUP_TNAME = "user_in_group";
	static final public String USERINGROUP_CIDUSER = "iduser";
	static final public String USERINGROUP_CIDLGROUP = "idgrouplocal";
	static final public String USERINGROUP_CSYNC = "sync";
	static final public String USERINGROUP_CTOREJECT = "toreject";
	static final public String USERINGROUP_CTOREMOVE = "toremove";
	static final private String TABLE_USERINGROUP_CREATE = "CREATE TABLE "+USERINGROUP_TNAME+"("+
			"  "+USERINGROUP_CIDUSER+" INTEGER NOT NULL,"+
			"  "+USERINGROUP_CIDLGROUP+" INTEGER NOT NULL,"+
			"  "+USERINGROUP_CSYNC+" BOOL NOT NULL DEFAULT 1,"+
			"  "+USERINGROUP_CTOREJECT+" BOOL NOT NULL DEFAULT 0,"+
			"  "+USERINGROUP_CTOREMOVE+" BOOL NOT NULL DEFAULT 0"+
			");";
	static final private String TABLE_USERINGROUP_DELETE = "DROP TABLE IF EXISTS "+USERINGROUP_TNAME;
	
	static final public String USER_TNAME = "user";
	static final public String USER_CID = "iduser";
	static final public String USER_CEMAIL = "email";
	static final public String USER_CPASSWORD = "password";
	static final public String USER_CNAME = "name";
	static final public String USER_CLASTUPDATE = "lastupdate";
	static final private String TABLE_USER_CREATION = "CREATE TABLE "+USER_TNAME+" ("+USER_CID+" INT PRIMARY KEY, "+USER_CEMAIL+" VARCHAR(100) , "+USER_CPASSWORD+" VARCHAR(200) , "+USER_CNAME+" VARCHAR(100), "+USER_CLASTUPDATE+" INT )";
	static final private String TABLE_USER_DELETE = "DROP TABLE IF EXISTS "+USER_TNAME;
	
	static final public String PLACE_TNAME = "place";
	static final public String PLACE_CIDPLACE = "idplace";
	static final public String PLACE_CNAME = "name";
	static final public String PLACE_CPLACETYPE = "place_type";
	static final public String PLACE_CHUMANLOCATION = "location_desc";
	static final public String PLACE_CLAT = "lat";
	static final public String PLACE_CLON = "lon";
	static final public String PLACE_CDESCR = "desc";
	static final public String PLACE_CRATE = "rate";
	static final public String TABLE_PLACE_CREATION = "CREATE TABLE "+PLACE_TNAME+" (" +
			PLACE_CIDPLACE+" INT  PRIMARY KEY, "+PLACE_CNAME+" VARCHAR(100) , "+
			PLACE_CPLACETYPE+" VARCHAR(10) , "+
			PLACE_CHUMANLOCATION+" VARCHAR(100) , "+PLACE_CLAT+" INT , "+PLACE_CLON+" INT, "+PLACE_CDESCR+" TEXT , "+PLACE_CRATE+" FLOAT )";
	static final public String TABLE_PLACE_DELETE = "DROP TABLE IF EXISTS "+PLACE_TNAME;
	
	static final public String PLACEINGROUP_TNAME = "place_in_group";
	static final public String PLACEINGROUP_CIDGROUP  = "idgroup";
	static final public String PLACEINGROUP_CIDPLACE = "idplace";
	static final public String PLACEINGROUP_CSYNC = "sync";
	static final public String PLACEINGROUP_CMARKTODEL = "marktodel";
	static final public String TABLE_PLACEINGROUP_CREATION = "CREATE TABLE "+PLACEINGROUP_TNAME+"("+
			"  "+PLACEINGROUP_CIDGROUP+" INTEGER NOT NULL,"+
			"  "+PLACEINGROUP_CIDPLACE+" INTEGER NOT NULL,"+
			"  "+PLACEINGROUP_CSYNC+" BOOL NOT NULL DEFAULT 0,"+
			"  "+PLACEINGROUP_CMARKTODEL+" BOOL NOT NULL DEFAULT 0,"+
			"  PRIMARY KEY("+PLACEINGROUP_CIDGROUP+","+PLACEINGROUP_CIDPLACE+")"+
			");"+ 
			
			//TODO put it separeted.
			"CREATE INDEX PLACEINGROUP_TNAME.CSYNC_INDEX ON "+PLACEINGROUP_TNAME+"("+PLACEINGROUP_CSYNC+");"+
			"CREATE INDEX PLACEINGROUP_TNAME.CMARKTODEL_INDEX ON "+PLACEINGROUP_TNAME+"("+PLACEINGROUP_CMARKTODEL+");";
	static final public String TABLE_PLACEINGROUP_DELETE = "DROP TABLE IF EXISTS "+PLACEINGROUP_TNAME;
	
	static final public String ENTRY_TNAME = "entry";
	static final public String ENTRY_CIDENTRYL = "identrylocal";
	static final public String ENTRY_CIDENTRY = "identry";
	static final public String ENTRY_CIDGROUPL = "idgrouplocal";
	static final public String ENTRY_CIDPLACE = "idplace";
	static final public String ENTRY_CIDAUTOR = "idautor";
	static final public String ENTRY_CFEELING = "feeling";
	static final public String ENTRY_COPTTEXT = "opttext";
	static final public String ENTRY_CCREATED = "created"; //Entry creation. Online creation will overwrite the local creation.
	static final public String ENTRY_CDATE = "date"; //Date of the context of the entry.
	static final public String ENTRY_CSYNC = "sync";
	static final public String ENTRY_CENDED = "ended";
	static final public String ENTRY_CLASTUPDATE = "lastupdate";
	static final public String TABLE_ENTRY_CREATION = "CREATE TABLE "+ENTRY_TNAME+"("+
			"  "+ENTRY_CIDENTRYL+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
			"  "+ENTRY_CIDENTRY+" INTEGER,"+
			"  "+ENTRY_CIDGROUPL+" INTEGER NOT NULL,"+
			"  "+ENTRY_CIDPLACE+" INTEGER NOT NULL,"+
			"  "+ENTRY_CFEELING+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+ENTRY_COPTTEXT+" TEXT NOT NULL DEFAULT '',"+
			"  "+ENTRY_CCREATED+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+ENTRY_CDATE+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+ENTRY_CSYNC+" INTEGER NOT NULL DEFAULT 0,"+
			"  "+ENTRY_CENDED+" INTEGER NOT NULL DEFAULT 0,"+
			"  "+ENTRY_CLASTUPDATE+" INTEGER NOT NULL DEFAULT -1,"+
			"  "+ENTRY_CIDAUTOR+" INTEGER NOT NULL,"+
			"  CONSTRAINT 'ENTRY_CIDENTRYL_UNIQUE'"+
			"    UNIQUE("+ENTRY_CIDENTRYL+"),"+
			"  CONSTRAINT 'ENTRY_CIDENTRY_UNIQUE'"+
			"    UNIQUE("+ENTRY_CIDENTRY+")"+
			")";
	static final public String TABLE_ENTRY_DELETE = "DROP TABLE IF EXISTS "+ENTRY_TNAME;
	
	static final public String PHOTO_TNAME = "photo";
	static final public String PHOTO_CIDL = "idphotolocal";
	static final public String PHOTO_CIDO = "idphotoonline";
	static final public String PHOTO_CIDENTRYL = "identrylocal";
	static final public String PHOTO_CLOCALDIR = "localdir";
	static final public String PHOTO_CURL = "url";
	static final public String PHOTO_CSYNC = "sync";
	static final public String PHOTO_CREQUESTED = "requested";
	static final public String TABLE_PHOTO_CREATION = "CREATE TABLE "+PHOTO_TNAME+" ("+
			PHOTO_CIDL+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "+
			PHOTO_CIDO+" INTEGER NOT NULL DEFAULT -1, "+
			PHOTO_CIDENTRYL+" INTEGER NOT NULL, "+
			PHOTO_CLOCALDIR+" TEXT NOT NULL DEFAULT '', "+
			PHOTO_CURL+" TEXT NOT NULL DEFAULT '', "+
			PHOTO_CSYNC+" BOOL NOT NULL DEFAULT 0 ,"+
			PHOTO_CREQUESTED+" BOOL NOT NULL DEFAULT 0"+
			" );";
	static final public String TABLE_PHOTO_DELETE = "DROP TABLE IF EXISTS "+PHOTO_TNAME;
	
	public TravelmeDatabase(Context context, int idUser) {
		super(context, DATABASE_NAME+idUser, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(TABLE_GROUP_CREATION);
		db.execSQL(TABLE_FRIEND_CREATION);
		db.execSQL(TABLE_USERINGROUP_CREATE);
		db.execSQL(TABLE_USER_CREATION);
		db.execSQL(TABLE_COMMENT_CREATION);
		db.execSQL(TABLE_PLACE_CREATION);
		db.execSQL(TABLE_PLACEINGROUP_CREATION);
		db.execSQL(TABLE_ENTRY_CREATION);
		db.execSQL(TABLE_PHOTO_CREATION);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int versionAnterior, int versionNueva) {
		//Problemas: Cuando se actualice la estructura de la base de datos *****el contenido, local, se borrará*****. Podría añadirse un ALTER TABLE para solucionarlo.
		db.execSQL(TABLE_GROUP_DELETE); //Eliminamos las tablas anteriores.
		db.execSQL(TABLE_GROUP_CREATION); //Recreamos las tablas nuevas
		
		db.execSQL(TABLE_FRIEND_DELETE);
		db.execSQL(TABLE_FRIEND_CREATION);
		
		db.execSQL(TABLE_USER_DELETE);
		db.execSQL(TABLE_USER_CREATION);
		
		db.execSQL(TABLE_COMMENT_DELETE);
		db.execSQL(TABLE_COMMENT_CREATION);
		
		db.execSQL(TABLE_USERINGROUP_DELETE);
		db.execSQL(TABLE_USERINGROUP_CREATE);
		
		db.execSQL(TABLE_PLACE_DELETE);
		db.execSQL(TABLE_PLACE_CREATION);
		
		db.execSQL(TABLE_PLACEINGROUP_DELETE);
		db.execSQL(TABLE_PLACEINGROUP_CREATION);

		db.execSQL(TABLE_ENTRY_DELETE);
		db.execSQL(TABLE_ENTRY_CREATION);
		
		db.execSQL(TABLE_PHOTO_CREATION);
		db.execSQL(TABLE_PHOTO_DELETE);
	}

}
